package org.example;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

public class ExcelGroupingTwo {

    public static void main(String[] args) {

        System.out.println("开始第二次排序.");

        String inputFilePath = "C:\\\\Users\\\\luke\\\\Desktop\\\\2.3.xlsx";
        String outputFilePath = "C:\\\\Users\\\\luke\\\\Desktop\\\\2.4.xlsx";

        try {
            FileInputStream fis = new FileInputStream(inputFilePath);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet sheet = workbook.getSheetAt(0);

            Map<String, List<Row>> groupedRows = new HashMap<>();

            // Iterate through the rows and group them based on the values in columns E, F, G, H, J, K, M, N, O
            for (Row row : sheet) {
                System.out.println("Column E");
                Cell cellE = row.getCell(4); // Column E
                System.out.println("Column F");
                Cell cellF = row.getCell(5); // Column F
                System.out.println("Column G");
                Cell cellG = row.getCell(6); // Column G
                System.out.println("Column H");
                Cell cellH = row.getCell(7); // Column H
                System.out.println("Column J");
                Cell cellJ = row.getCell(9); // Column J
                System.out.println("Column K");
                Cell cellK = row.getCell(10); // Column K
                System.out.println("Column M");
                Cell cellM = row.getCell(12); // Column M
                System.out.println("// Column N");
                Cell cellN = row.getCell(13); // Column N
                System.out.println("Column O");
                Cell cellO = row.getCell(14); // Column O

                String key = getStringValueFromCell(cellE) + getStringValueFromCell(cellF)
                        + getStringValueFromCell(cellG) + getStringValueFromCell(cellH)
                        + getStringValueFromCell(cellJ) + getStringValueFromCell(cellK)
                        + getStringValueFromCell(cellM) + getStringValueFromCell(cellN)
                        + getStringValueFromCell(cellO);

                List<Row> group = groupedRows.getOrDefault(key, new ArrayList<>());
                group.add(row);
                groupedRows.put(key, group);
            }

            // 遍历分组行，保留最后一行并合并前一行的数据
            for (List<Row> group : groupedRows.values()) {
                System.out.println("遍历分组行，保留最后一行并合并前一行的数据");
                if (group.size() > 1) {
                    Row lastRow = group.get(group.size() - 1);
                    Cell cellD = lastRow.getCell(3); // Column D
                    Cell cellI = lastRow.getCell(8); // Column I
                    StringBuilder mergedData = new StringBuilder(getStringValueFromCell(cellD));
                    double totalIValue = cellI.getNumericCellValue();

                    for (int i = 0; i < group.size() - 1; i++) {
                        Row rowToDelete = group.get(i);
                        Cell cellDToDelete = rowToDelete.getCell(3); // Column D
                        Cell cellIToDelete = rowToDelete.getCell(8); // Column I
                        mergedData.append(",").append(getStringValueFromCell(cellDToDelete));
                        totalIValue += cellIToDelete.getNumericCellValue();
                        sheet.removeRow(rowToDelete);
                    }

                    cellD.setCellValue(mergedData.toString());
                    cellI.setCellValue(totalIValue);
                }
            }

            fis.close();

            // Write the updated workbook to output file
            System.out.println("将更新后的工作簿写入输出文件");
            FileOutputStream fos = new FileOutputStream(outputFilePath);
            workbook.write(fos);
            workbook.close();
            fos.close();

            System.out.println("第二次排序完成.");

        } catch (IOException e) {
            e.printStackTrace();
        }

        String inputFile = "C:\\\\Users\\\\luke\\\\Desktop\\\\2.4.xlsx"; // 输入Excel文件名
        String outputFile = "C:\\\\Users\\\\luke\\\\Desktop\\\\2.5.xlsx"; // 输出Excel文件名
        ExcelUtility.removeBlankRows(inputFile, outputFile);

    }

    private static String getStringValueFromCell(Cell cell) {
        if (cell == null) return "";
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue().trim();
    }
}
